package com.android.tripspins.app.dao;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.format.DateFormat;

import com.android.tripspins.app.bo.CurrentWeather;
import com.android.tripspins.app.bo.Pin;
import com.google.android.gms.maps.model.LatLng;

import java.text.SimpleDateFormat;
import java.util.ArrayList;

/**
 * Created by João.Rutkoski on 18/06/13.
 */
public class PinDAO {
    private SQLiteDatabase database;
    private TripDAO tripDAO;
    private AttachmentDAO attDao;
    private CurrentWeatherDAO currentWeatherDAO;
    private CurrentWeather currentWeather;


    public PinDAO(SQLiteDatabase database) {
        this.database = database;
    }

    public long persist(long tripId, Pin pin) {

        if(pin.getId() == -1)
        {
            return insertPin(tripId, pin);
        }
        else
        {
            return updatePin(pin);
        }
    }

    private long insertPin(long tripId, Pin pin) {

        StringBuilder sbInsert = new StringBuilder();
        sbInsert.append("insert into pins ( id_trip ");
        sbInsert.append(                 ", dt_pin ");
        sbInsert.append(                 ", num_latitude ");
        sbInsert.append(                 ", num_longitude ");
        sbInsert.append(                 ", txt_note ");
        sbInsert.append(                 ", txt_title) ");
        sbInsert.append(          "values ( ? ");
        sbInsert.append(                 ", ? ");
        sbInsert.append(                 ", ? ");
        sbInsert.append(                 ", ? ");
        sbInsert.append(                 ", ? ");
        sbInsert.append(                 ", ? );");

        database.execSQL(sbInsert.toString(),new String[]{
                String.valueOf(tripId),
                DateFormat.format("dd/MM/yyyy HH:mm:ss", pin.getDate()).toString(),
                String.valueOf(pin.getLatitude()),
                String.valueOf(pin.getLongitude()),
                pin.getTextNote(),
                pin.getTitle()});

        long pinId = getLastInsertedID();
        if(pin.getCurrentWeather() != null){
            CurrentWeatherDAO currentWeatherDAO = new CurrentWeatherDAO(database);
            currentWeatherDAO.persist(pinId, pin.getCurrentWeather());
        }
        if(pin.getAddress() != null){
            AddressDAO addressDAO = new AddressDAO(database);
            addressDAO.persist(pinId, pin.getAddress());
        }

        return pinId;
    }

    private long updatePin(Pin pin) {
        StringBuilder sbUpdate = new StringBuilder();
        sbUpdate.append("update pins ");
        sbUpdate.append(   "set txt_synced = 'Y' ");
        sbUpdate.append("where _id = ?  ");

        database.execSQL(sbUpdate.toString(),new String[]{
                String.valueOf(pin.getId())});
        if (pin.getAttachments()!= null && !pin.getAttachments().isEmpty() )
        {
            attDao = new AttachmentDAO(database);
            attDao.persist(pin.getId(), pin.getAttachments());
        }
        return pin.getId();
    }


    private long getLastInsertedID() {
        String query = "Select last_insert_rowid() from pins";
        Cursor cursor = database.rawQuery(query, null);
        cursor.moveToFirst();
        long id = cursor.getInt(0);
        cursor.close();
        return id;
    }


    public void persist(long tripId, ArrayList<Pin> pins) {
        for (Pin p : pins) {
            persist(tripId, p);
        }
    }


    public ArrayList<Pin> getPins(long tripId) {
        attDao = new AttachmentDAO(database);
        currentWeatherDAO = new CurrentWeatherDAO(database);
        AddressDAO addressDAO = new AddressDAO(database);
        ArrayList<Pin> pins = new ArrayList<Pin>();
        StringBuilder sb = new StringBuilder();
        sb.append("Select _id");
        sb.append(     ", dt_pin ");
        sb.append(     ", txt_title ");
        sb.append(     ", txt_note ");
        sb.append(     ", num_latitude ");
        sb.append(     ", num_longitude ");
        sb.append(" From pins");
        sb.append(" Where id_trip = ?");
        sb.append(" order by 1 ");

        Cursor cursorPin = database.rawQuery(sb.toString(),
                new String[] { String.valueOf(tripId) });
        cursorPin.moveToFirst();
        while (!cursorPin.isAfterLast()) {
            Pin p = cursorToPin(cursorPin);
            p.setAttachments(attDao.getAttachmentsByPinId(p.getId()));
            p.setCurrentWeather(currentWeatherDAO.getCurrentWeather(p.getId()));
            p.setAddress(addressDAO.getAddress(p.getId()));
            pins.add(p);
            cursorPin.moveToNext();
        }
        cursorPin.close();
        return pins;
    }


    private Pin cursorToPin(Cursor cursor) {
        Pin p = new Pin();
        p.setId(cursor.getLong(0));
        SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
        try {
            p.setDate(format.parse(cursor.getString(1)));
        } catch (Exception ex) {
            p.setDate(null);
        }
        p.setTitle(cursor.getString(2));
        p.setTextNote(cursor.getString(3));
        p.setLatLng(new LatLng(cursor.getDouble(4), cursor.getDouble(5)));
        return p;
    }

    public ArrayList<Pin> getPinsToSync(long tripId) {
        attDao = new AttachmentDAO(database);
        currentWeatherDAO = new CurrentWeatherDAO(database);
        AddressDAO addressDAO = new AddressDAO(database);
        ArrayList<Pin> pins = new ArrayList<Pin>();
        StringBuilder sb = new StringBuilder();
        sb.append("Select _id");
        sb.append(     ", dt_pin ");
        sb.append(     ", txt_title ");
        sb.append(     ", txt_note ");
        sb.append(     ", num_latitude ");
        sb.append(     ", num_longitude ");
        sb.append(" From pins");
        sb.append(" Where id_trip = ?");
        sb.append("   and txt_synced = 'N'");
        sb.append(" order by 1 ");

        Cursor cursorPin = database.rawQuery(sb.toString(),
                new String[] { String.valueOf(tripId) });
        cursorPin.moveToFirst();
        while (!cursorPin.isAfterLast()) {
            Pin p = cursorToPin(cursorPin);
            p.setAttachments(attDao.getAttachmentsToSyncByPinId(p.getId()));
            p.setCurrentWeather(currentWeatherDAO.getCurrentWeatherToSync(p.getId()));
            p.setAddress(addressDAO.getAddressToSync(p.getId()));
            pins.add(p);
            cursorPin.moveToNext();
        }
        cursorPin.close();
        return pins;
    }

    public ArrayList<Pin> getPinsSynced(long tripId) {
        attDao = new AttachmentDAO(database);
        currentWeatherDAO = new CurrentWeatherDAO(database);
        AddressDAO addressDAO = new AddressDAO(database);
        ArrayList<Pin> pins = new ArrayList<Pin>();
        StringBuilder sb = new StringBuilder();
        sb.append("Select _id");
        sb.append(     ", dt_pin ");
        sb.append(     ", txt_title ");
        sb.append(     ", txt_note ");
        sb.append(     ", num_latitude ");
        sb.append(     ", num_longitude ");
        sb.append(" From pins");
        sb.append(" Where id_trip = ?");
        sb.append("   and txt_synced = 'Y'");
        sb.append(" order by 1 ");

        Cursor cursorPin = database.rawQuery(sb.toString(),
                new String[] { String.valueOf(tripId) });
        cursorPin.moveToFirst();
        while (!cursorPin.isAfterLast()) {
            Pin p = cursorToPin(cursorPin);
            p.setAttachments(attDao.getAttachmentsSyncedByPinId(p.getId()));
            p.setCurrentWeather(currentWeatherDAO.getCurrentWeatherSynced(p.getId()));
            p.setAddress(addressDAO.getAddressSynced(p.getId()));
            pins.add(p);
            cursorPin.moveToNext();
        }
        cursorPin.close();
        return pins;
    }
}
